﻿namespace sfcms.api
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class database
    {
        private static string _ConnectionString;
        private SqlCommand _InstanceCommand;
        private SqlConnection _InstanceConnection;
        private string _InstanceConnectionString;
        private static SortedList<string, DataTable> _SchemaCache = new SortedList<string, DataTable>();

        internal database()
        {
            this._InstanceConnectionString = _ConnectionString;
        }

        internal database(string instanceConnectionString)
        {
            this._InstanceConnectionString = instanceConnectionString;
        }

        internal void Close()
        {
            try
            {
                this._InstanceConnection.Close();
            }
            catch
            {
            }
            try
            {
                this._InstanceConnection.Dispose();
            }
            catch
            {
            }
            this._InstanceConnection = null;
            try
            {
                this._InstanceCommand.Dispose();
            }
            catch
            {
            }
            this._InstanceCommand = null;
        }

        public static string CreateTypedParameterName(string parameterName, DbType parameterType)
        {
            return ("[DbType=" + parameterType.ToString() + "]" + parameterName);
        }

        internal DataTable ExecuteDataTable(string sqlQuery, params object[] parameters)
        {
            DataTable variable0000;
            SqlDataAdapter dataAdapter = this.GetDataAdapter(sqlQuery, parameters);
            try
            {
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                dataAdapter.FillSchema(dataTable, SchemaType.Source);
                variable0000 = dataTable;
            }
            finally
            {
                this.Close();
            }
            return variable0000;
        }

        internal int ExecuteNonQuery(string sqlQuery, params object[] parameters)
        {
            int variable0000;
            SqlCommand command = this.GetCommand(sqlQuery, parameters);
            try
            {
                variable0000 = command.ExecuteNonQuery();
            }
            finally
            {
                this.Close();
            }
            return variable0000;
        }

        internal object ExecuteScalar(string sqlQuery, params object[] parameters)
        {
            object variable0000;
            SqlCommand command = this.GetCommand(sqlQuery, parameters);
            try
            {
                variable0000 = command.ExecuteScalar();
            }
            finally
            {
                this.Close();
            }
            return variable0000;
        }

        internal DataTable ExecuteSPGetDataTable(string sqlQuery, params object[] parameters)
        {
            DataTable variable0000;
            SqlDataAdapter dataAdapter = this.GetDataAdapter(sqlQuery, parameters, false);
            try
            {
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                dataAdapter.FillSchema(dataTable, SchemaType.Source);
                variable0000 = dataTable;
            }
            finally
            {
                this.Close();
            }
            return variable0000;
        }

        internal int ExecuteSPNonQuery(string sqlQuery, params object[] parameters)
        {
            int variable0000;
            SqlCommand command = this.GetCommand(sqlQuery, parameters, false);
            try
            {
                variable0000 = command.ExecuteNonQuery();
            }
            finally
            {
                this.Close();
            }
            return variable0000;
        }

        private SqlCommand GetCommand(string commandText, object[] parameters)
        {
            return this.GetCommand(commandText, parameters, true);
        }

        private SqlCommand GetCommand(string commandText, object[] parameters, bool isText)
        {
            if (this._InstanceCommand != null)
            {
                throw new InvalidOperationException("Only one command can be executed through a Database instance");
            }
            this._InstanceCommand = new SqlCommand();
            this._InstanceCommand.Connection = this.GetConnection();
            this._InstanceCommand.CommandType = isText ? CommandType.Text : CommandType.StoredProcedure;
            this._InstanceCommand.CommandText = commandText;
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i += 2)
                {
                    this._InstanceCommand.Parameters.Add(GetParameter(Convert.ToString(parameters[i]), parameters[i + 1]));
                }
            }
            return this._InstanceCommand;
        }

        private SqlConnection GetConnection()
        {
            SqlConnection variable0000;
            if (this._InstanceConnection != null)
            {
                throw new InvalidOperationException("Only one command can be executed through a Database instance");
            }
            if (string.IsNullOrEmpty(this._InstanceConnectionString))
            {
                throw new InvalidOperationException("The connection string has not been initialized");
            }
            this._InstanceConnection = new SqlConnection(this._InstanceConnectionString);
            try
            {
                this._InstanceConnection.Open();
                variable0000 = this._InstanceConnection;
            }
            catch
            {
                this.Close();
                throw;
            }
            return variable0000;
        }

        private SqlDataAdapter GetDataAdapter(string commandText, object[] parameters)
        {
            return this.GetDataAdapter(commandText, parameters, true);
        }

        private SqlDataAdapter GetDataAdapter(string commandText, object[] parameters, bool isText)
        {
            return new SqlDataAdapter(this.GetCommand(commandText, parameters, isText));
        }

        private static SqlParameter GetParameter(string parameterName, object parameterValue)
        {
            SqlParameter newParameter = new SqlParameter();
            if (parameterName.StartsWith("[DbType="))
            {
                int indexOfCloseBracket = parameterName.IndexOf(']');
                int indexOfTypeStart = parameterName.IndexOf('=') + 1;
                string typeName = parameterName.Substring(indexOfTypeStart, indexOfCloseBracket - indexOfTypeStart);
                newParameter.DbType = (DbType) Enum.Parse(typeof(DbType), typeName);
                newParameter.ParameterName = parameterName.Substring(indexOfCloseBracket + 1);
            }
            else
            {
                newParameter.ParameterName = parameterName;
            }
            newParameter.Value = (parameterValue == null) ? DBNull.Value : parameterValue;
            return newParameter;
        }

        internal DataTable ReadTableSchema(string tableName)
        {
            return this.ReadTableSchema("dbo", tableName);
        }

        internal DataTable ReadTableSchema(string schemaName, string tableName)
        {
            if (!_SchemaCache.ContainsKey(schemaName + "." + tableName))
            {
                try
                {
                    DataTable dataTable = this.ExecuteDataTable("SELECT * FROM [" + schemaName + "].[" + tableName + "] (NOLOCK) WHERE 1 = @dummy", new object[] { "@dummy", 2 });
                    _SchemaCache.Add(schemaName + "." + tableName, dataTable);
                }
                finally
                {
                    this.Close();
                }
            }
            return _SchemaCache[schemaName + "." + tableName].Copy();
        }

        internal DataTable SelectFromTable(string tableName)
        {
            return this.SelectFromTable(tableName, new object[0]);
        }

        internal DataTable SelectFromTable(string schemaName, string tableName)
        {
            return this.SelectFromTable(schemaName, tableName, null);
        }

        internal DataTable SelectFromTable(string tableName, object[] parameters)
        {
            return this.SelectFromTable("dbo", tableName, parameters);
        }

        internal DataTable SelectFromTable(string schemaName, string tableName, object[] parameters)
        {
            StringBuilder sqlQuery = new StringBuilder();
            List<object> parameterNameAndValues = new List<object>();
            sqlQuery.AppendFormat("SELECT * FROM [{0}].[{1}] (NOLOCK)\r\n", schemaName, tableName);
            if ((parameters != null) && (parameters.Length > 1))
            {
                sqlQuery.Append("WHERE\r\n");
                for (int i = 0; i < parameters.Length; i += 2)
                {
                    sqlQuery.AppendFormat("[{0}] = @p_{1} AND ", parameters[i], i / 2);
                    parameterNameAndValues.Add("@p_" + (i / 2));
                    parameterNameAndValues.Add(parameters[i + 1]);
                }
                sqlQuery.Length -= 5;
            }
            return this.ExecuteDataTable(sqlQuery.ToString(), parameterNameAndValues.ToArray());
        }

        public static void SetConnectionString(string connectionString)
        {
            _ConnectionString = connectionString;
        }
    }
}

